package com.hhq.demo.dao.impl;

import java.sql.*;
import java.util.*;

import com.hhq.demo.bean.Express;
import com.hhq.demo.bean.RankingBean;
import com.hhq.demo.dao.ExpressDao;
import com.hhq.demo.exception.DuplicateCodeException;
import com.hhq.demo.utils.DruidUtil;

/**
 * @author HHQ
 * @date 2021年12月12日
 */
public class ExpressDaoImpl implements ExpressDao {
    private static final String CONSOLE_DATA = "SELECT " +
            "count(ID) data1_size," +
            "count(TO_DAYS(in_time)=TO_DAYS(now()) or NULL)  data1_day," +
            "count(status=0 or null) data2_size," +
            "count(TO_DAYS(in_time)=TO_DAYS(now()) and status=0 or null) data2_day " +
            "FROM express";
    private final String RANK_EXPRESS_BY_TOTAL = "select COUNT(phone) num,u.phone,u.username from express e,user u where e.receive_phone=u.phone GROUP BY phone ORDER BY num desc limit 0,10";
    private final String RANK_EXPRESS_BY_YEAR = "select COUNT(phone) num,u.phone,u.username from express e,user u where e.receive_phone=u.phone and (TO_DAYS(NOW())-TO_DAYS(e.in_time))<360 GROUP BY phone ORDER BY num desc limit 0,10";
    private final String RANK_EXPRESS_BY_MONTH = "select COUNT(phone) num,u.phone,u.username from express e,user u where e.receive_phone=u.phone and (TO_DAYS(NOW())-TO_DAYS(e.in_time))<30 GROUP BY phone ORDER BY num desc limit 0,10";
    private final String FIND_BY_CODE = "select * from express where code=?";
    private final String FIND_BY_PHONE = "select * from express where receive_phone=?";
    private final String LIST_LIMIT = "select * from express limit ?,?";
    private final String LIST_ALL = "select * from express";
    private final String DELETE_BY_NUMBER = "delete from express where number=?";
    private final String INSERT = "insert into express values(null,?,?,?,?,?,0,?,now(),null)";
    private final String FIND_BY_NUMBER = "select * from express where number=?";
    private final String UPDATE_BY_ID = "update express set number=?,company=?,receive_name=?,status=? where id=?";
    private final String DELETE_BY_CODE = "delete from express where code=?";
    private final String UPDATE_PHONE_BY_PHONE = "update express set phone=? where receive_phone=?";
    private final String UPDATE_STATUS_BY_CODE = "update express set status=1,out_time=now() where code=?";


    /**
     * 获取控制台数据
     *
     * @return 快递总数，新增快递数，待取件数，新增代取件数
     */
    @Override
    public Map<String, Object> consoleData() {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Map<String, Object> map = null;
        try {
            statement = connection.prepareStatement(CONSOLE_DATA);
            resultSet = statement.executeQuery();
            if (resultSet.next()) {
                map = new HashMap<>();
                map.put("data1_size", resultSet.getInt("data1_size"));
                map.put("data1_day", resultSet.getInt("data1_day"));
                map.put("data2_size", resultSet.getInt("data2_size"));
                map.put("data2_day", resultSet.getInt("data2_day"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, statement, resultSet);
        }
        return map;
    }

    /**
     * 查询所有快递信息
     *
     * @return 快递集合
     */
    @Override
    public List<Express> listAll() {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Express> expressList = new ArrayList<>();
        try {
            statement = connection.prepareStatement(LIST_ALL);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setCompany(resultSet.getString("company"));
                express.setReceiveName(resultSet.getString("receive_name"));
                express.setReceivePhone(resultSet.getString("receive_phone"));
                express.setRecordPhone(resultSet.getString("record_phone"));
                express.setStatus(resultSet.getInt("status"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getDate("in_time"));
                express.setOutTime(resultSet.getDate("out_time"));
                expressList.add(express);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, statement, resultSet);
        }
        return expressList;
    }

    /**
     * 分页查询快递信息
     *
     * @param offset     起始索引
     * @param pageNumber 每页显示数量
     * @return 快递集合
     */
    @Override
    public List<Express> listLimit(Integer offset, Integer pageNumber) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Express> expressList = new ArrayList<>();
        try {
            statement = connection.prepareStatement(LIST_LIMIT);
            statement.setInt(1, offset);
            statement.setInt(2, pageNumber);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setCompany(resultSet.getString("company"));
                express.setReceiveName(resultSet.getString("receive_name"));
                express.setReceivePhone(resultSet.getString("receive_phone"));
                express.setRecordPhone(resultSet.getString("record_phone"));
                express.setStatus(resultSet.getInt("status"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getDate("in_time"));
                express.setOutTime(resultSet.getDate("out_time"));
                expressList.add(express);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, statement, resultSet);
        }
        return expressList;
    }

    /**
     * 根据快递单号删除
     *
     * @param number 单号
     * @return 受影响的行数
     */
    @Override
    public int deleteByNumber(String number) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(DELETE_BY_NUMBER);
            statement.setObject(1, number);
            return statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, statement, null);
        }
        return 0;
    }

    /**
     * 新增快递
     *
     * @param express 快递
     * @return 受影响的行数
     * "insert into express values(null,?,?,?,?,?,1,?,now(),null)";
     */
    @Override
    public int insert(Express express) throws DuplicateCodeException {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(INSERT);
            statement.setObject(1, express.getNumber());
            statement.setObject(2, express.getCompany());
            statement.setObject(3, express.getReceiveName());
            statement.setObject(4, express.getReceivePhone());
            statement.setObject(5, express.getRecordPhone());
            statement.setObject(6, express.getCode());
            return statement.executeUpdate();
        } catch (SQLException e) {
            if (e.getMessage().endsWith("for key 'code'")) {
                //是因为取件码重复,而出现了异常
                throw new DuplicateCodeException(e.getMessage());
            } else {
                e.printStackTrace();
            }
        } finally {
            DruidUtil.close(connection, statement, null);
        }
        return 0;
    }

    /**
     * 根据单号查询快递信息
     *
     * @param number 单号
     * @return 快递信息
     */
    @Override
    public Express findByNumber(String number) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Express express = null;
        try {
            statement = connection.prepareStatement(FIND_BY_NUMBER);
            statement.setObject(1, number);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setCompany(resultSet.getString("company"));
                express.setReceiveName(resultSet.getString("receive_name"));
                express.setReceivePhone(resultSet.getString("receive_phone"));
                express.setRecordPhone(resultSet.getString("record_phone"));
                express.setStatus(resultSet.getInt("status"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getDate("in_time"));
                express.setOutTime(resultSet.getDate("out_time"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, statement, resultSet);
        }
        return express;
    }

    /**
     * 修改快递信息
     *
     * @param id      要修改的快递的id
     * @param express 要修改的快递的信息
     * @return 受影响的行数
     * update express set number=?,company=?,receive_name=?,status=? where id=?
     */
    @Override
    public int update(Integer id, Express express) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(UPDATE_BY_ID);
            preparedStatement.setObject(1, express.getNumber());
            preparedStatement.setObject(2, express.getCompany());
            preparedStatement.setObject(3, express.getReceiveName());
            preparedStatement.setObject(4, express.getStatus());
            preparedStatement.setObject(5, id);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }
        return 0;
    }

    /**
     * 根据取件码删除快递
     *
     * @param code 取件码
     * @return 受影响的行数
     */
    @Override
    public int deleteByCode(String code) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(DELETE_BY_CODE);
            preparedStatement.setObject(1, code);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }
        return 0;
    }

    /**
     * 根据手机号码查找快递
     *
     * @param phone 手机号码
     * @return 快递集合
     */
    @Override
    public List<Express> findByPhone(String phone) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Express> expressList = new ArrayList<>();
        try {
            preparedStatement = connection.prepareStatement(FIND_BY_PHONE);
            preparedStatement.setObject(1, phone);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Express express = new Express();
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setCompany(resultSet.getString("company"));
                express.setReceiveName(resultSet.getString("receive_name"));
                express.setReceivePhone(resultSet.getString("receive_phone"));
                express.setRecordPhone(resultSet.getString("record_phone"));
                express.setStatus(resultSet.getInt("status"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getDate("in_time"));
                express.setOutTime(resultSet.getDate("out_time"));
                expressList.add(express);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return expressList;
    }

    /**
     * 根据取件码改变快递的状态
     *
     * @param code 取件码
     * @return 受影响的行数
     */
    @Override
    public int updateStatus(String code) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(UPDATE_STATUS_BY_CODE);
            preparedStatement.setObject(1, code);
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, null);
        }
        return 0;
    }

    /**
     * 根据取件码查询快递
     *
     * @param code 取件码
     * @return 快递信息
     */
    @Override
    public Express findByCode(String code) {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Express express = new Express();
        try {
            preparedStatement = connection.prepareStatement(FIND_BY_CODE);
            preparedStatement.setObject(1, code);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                express.setId(resultSet.getInt("id"));
                express.setNumber(resultSet.getString("number"));
                express.setCompany(resultSet.getString("company"));
                express.setReceiveName(resultSet.getString("receive_name"));
                express.setReceivePhone(resultSet.getString("receive_phone"));
                express.setRecordPhone(resultSet.getString("record_phone"));
                express.setStatus(resultSet.getInt("status"));
                express.setCode(resultSet.getString("code"));
                express.setInTime(resultSet.getDate("in_time"));
                express.setOutTime(resultSet.getDate("out_time"));
            }
            return express;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    /**
     * 总排名
     *
     * @return 排名结果
     */
    @Override
    public List<RankingBean> rankExpressNumber() {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ArrayList<RankingBean> rankingList = new ArrayList<>();
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(RANK_EXPRESS_BY_TOTAL);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                rankingList.add(new RankingBean(resultSet.getString("username"),resultSet.getInt("num"),resultSet.getString("phone")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return rankingList;
    }

    /**
     * 年排名
     *
     * @return 排名结果
     */
    @Override
    public List<RankingBean> rankExpressNumberToYear() {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ArrayList<RankingBean> rankingList = new ArrayList<>();
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(RANK_EXPRESS_BY_YEAR);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                rankingList.add(new RankingBean(resultSet.getString("username"),resultSet.getInt("num"),resultSet.getString("phone")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return rankingList;
    }

    /**
     * 月排名
     *
     * @return 排名结果
     */
    @Override
    public List<RankingBean> rankExpressNumberToMonth() {
        Connection connection = DruidUtil.getConnection();
        PreparedStatement preparedStatement = null;
        ArrayList<RankingBean> rankingList = new ArrayList<>();
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(RANK_EXPRESS_BY_MONTH);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                rankingList.add(new RankingBean(resultSet.getString("username"),resultSet.getInt("num"),resultSet.getString("phone")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DruidUtil.close(connection,preparedStatement,resultSet);
        }
        return rankingList;
    }
}
